﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CarolLib;
using CarolLib.Core.Helper;
using System.Data.SqlClient;
using System.Configuration;
using DispatchAssist.BusinessLogical.Model;
using DispatchAssist.Model;
using System.Data;

namespace DispatchAssist.BusinessLogical.DAL
{
    public class SqlDataProvider : IDataProvider
    {

        private static log4net.ILog Logger = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);


        #region Conn

        private static string connectionString;

        private SqlConnection GetSqlConnection()
        {
            return new SqlConnection(connectionString);
        }

        public SqlDataProvider()
        {
            try
            {
                connectionString = ConfigurationManager.ConnectionStrings["DispatchAssist"].ConnectionString;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        #endregion

        #region 吴中奇

        #region AlertMsg

        public List<MeteoMsg> GetUnCreateMeteoMsgList(int selectStatus, int exceptStatus)
        {


            List<MeteoMsg> result = null;

            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetUnCreateMeteoMsg",
                                                                  new SqlParameter("@status", selectStatus),
                                                                  new SqlParameter("@exceptStatus", exceptStatus)))
                {
                    if (dr.HasRows)
                    {
                        result = DTOHelper.CreateModelList<MeteoMsg>(dr);
                    }
                    else
                    {
                        result = new List<MeteoMsg>();
                    }
                    dr.Close();
                }
            }
            return result;
        }

        public List<AlertRule> GetAlertRuleByPara(string airPort, int level)
        {
            List<AlertRule> result = null;
            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetAlertRuleByPara",
                    new SqlParameter("@airPort", string.IsNullOrEmpty(airPort) ? (object)DBNull.Value : (object)airPort),
                    new SqlParameter("@level", level == -1 ? (object)DBNull.Value : (object)level)))
                {
                    if (dr.HasRows)
                    {
                        result = DTOHelper.CreateModelList<AlertRule>(dr);
                    }
                    else
                    {
                        result = new List<AlertRule>();
                    }
                    dr.Close();
                }
            }
            return result;
        }

        public List<EarlyWarning> GetEarlyTimeByPara(string airPort)
        {
            List<EarlyWarning> result = null;
            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetEarlyWarningByPara",
                                                                        new SqlParameter("@Airport", string.IsNullOrEmpty(airPort) ? (object)DBNull.Value : (object)airPort)))
                {
                    if (dr.HasRows)
                    {
                        result = DTOHelper.CreateModelList<EarlyWarning>(dr);
                    }
                    else
                    {
                        result = new List<EarlyWarning>();
                    }
                    dr.Close();
                }
            }
            return result;
        }

        public List<SeatMonitor> GetSeatMonitorByPara(string TailNo, string SeatID)
        {
            List<SeatMonitor> result = null;
            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetSeatMonitorByPara",
                                                                        new SqlParameter("@TailNo", string.IsNullOrEmpty(TailNo) ? (object)DBNull.Value : (object)TailNo)
                                                                        , new SqlParameter("@SeatID", string.IsNullOrEmpty(SeatID) ? (object)DBNull.Value : (object)SeatID)))
                {
                    if (dr.HasRows)
                    {
                        result = DTOHelper.CreateModelList<SeatMonitor>(dr);
                    }
                    else
                    {
                        result = new List<SeatMonitor>();
                    }
                    dr.Close();
                }
            }
            return result;
        }

        public Alert CreateAlert(Alert alert)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                //SqlParameter spAlertID = new SqlParameter("@AlertID", SqlDbType.UniqueIdentifier);
                //spAlertID.Direction = ParameterDirection.Output;
                //spAlertID.Value = alert.AlertID;

                SqlHelper.ExecuteNonQuery(conn, "up_CreateAlert"
                    // spAlertID
                    , new SqlParameter("@MetroMsgID", alert.MetroMsgID)
                    , new SqlParameter("@FlightInfoID", alert.FlightInfoID)
                    , new SqlParameter("@AirportRelation", alert.AirportRelation)
                    , new SqlParameter("@AlertTime", alert.AlertTime)
                    , new SqlParameter("@AlertActionFlag", alert.AlertActionFlag)
                    , new SqlParameter("@PerformActionFlag", alert.PerformActionFlag)
                    , new SqlParameter("@SeatID", alert.SeatID)
                    , new SqlParameter("@LastModifyTime", alert.LastModifyTime)
                    , new SqlParameter("@DeleteFlag", alert.DeleteFlag)
                );
                // alert.AlertID = (Guid)spAlertID.Value;
                return alert;
            }
        }

        public void UpdateMeteoStatus(List<string> Ids, Enums.MeteoMsgStatus status)
        {
            if (Ids.Count == 0)
            {
                return;
            }
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_UpdateMeteoStatus", new SqlParameter("@Ids", SqlHelper.ConvertIdsToXML<string>("e", Ids.ToArray())), new SqlParameter("status", status));
            }
        }

        public List<FlightInfo> GetFligetInfoByMtMsg(string airPort, DateTime effecTime)
        {
            List<FlightInfo> result = null;
            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetFlightInfoByMtMsg",
                                                                        new SqlParameter("@airPort", airPort),
                                                                        new SqlParameter("@effectTime", effecTime)))
                {
                    if (dr.HasRows)
                    {
                        result = DTOHelper.CreateModelList<FlightInfo>(dr);
                    }
                    else
                    {
                        result = new List<FlightInfo>();
                    }
                    dr.Close();
                }
            }
            return result;
        }

        public List<ACARSMsg> GetSendAcarsMsgList()
        {
            return GetModelList<ACARSMsg>("up_GetAcarsMsgList");
        }

        public void UpdateAcarsMsgStatus(string[] IDs, int Status)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_UpdateAcarsMsgtatus",
                                                new SqlParameter("@ids", SqlHelper.ConvertIdsToXML<string>("e", IDs)),
                                                new SqlParameter("status", Status));
            }
        }

        #endregion

        #region Create message by templete

        public List<AlertAllInfo> GetNecessaryAlertInfo(Guid alertID, int selectFlag, int updateFlag)
        {
            //return GetModelList<AlertAllInfo>("up_GetNecessaryAlertInfo");

            List<AlertAllInfo> result = null;

            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetNecessaryAlertInfo",
                    new SqlParameter("@alertID", alertID == Guid.Empty ? (object)DBNull.Value : (object)alertID),
                    new SqlParameter("@selectFlag", selectFlag),
                    new SqlParameter("@updateFlag", updateFlag)))
                {
                    if (dr.HasRows)
                    {
                        result = DTOHelper.CreateModelList<AlertAllInfo>(dr);
                    }
                }
            }

            return result;

        }

        public List<MsgTemplate> GetMsgTemplate(string ID, int AlertAction, string TargetType)
        {
            List<MsgTemplate> result = null;

            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetMsgTemplete",
                    new SqlParameter("@id", string.IsNullOrEmpty(ID) ? (object)DBNull.Value : (object)ID),
                    new SqlParameter("@alertAction", AlertAction == -1 ? (object)DBNull.Value : AlertAction),
                    new SqlParameter("@type", string.IsNullOrEmpty(TargetType) ? (object)DBNull.Value : (object)TargetType)))
                {
                    if (dr.HasRows)
                    {
                        result = DTOHelper.CreateModelList<MsgTemplate>(dr);
                    }
                }
            }

            return result;
        }

        public List<ACARSDevice> GetDeviceModel(string tailNo)
        {
            List<ACARSDevice> result = null;

            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetDeviceType",
                    new SqlParameter("@tailNo", string.IsNullOrEmpty(tailNo) ? (object)DBNull.Value : (object)tailNo)))
                {
                    if (dr.HasRows)
                    {
                        result = DTOHelper.CreateModelList<ACARSDevice>(dr);
                    }
                }
            }

            return result;
        }

        public ACARSMsg InsertACARSMsgModel(ACARSMsg aCARSMsg)
        {
            using (SqlConnection conn = GetSqlConnection())
            {

                SqlHelper.ExecuteNonQuery(conn, "up_CreateACARSMsg"
                    , new SqlParameter("@AlertID", aCARSMsg.AlertID)
                    , new SqlParameter("@ReceiveTailNo", aCARSMsg.ReceiveTailNo)
                    , new SqlParameter("@MsgBody", aCARSMsg.MsgBody.Length > 512 ? aCARSMsg.MsgBody.Substring(0, 512) : aCARSMsg.MsgBody)
                    , new SqlParameter("@MsgStatus", aCARSMsg.MsgStatus)

                );
                return aCARSMsg;
            }
        }

        public void UpdateAlertStatus(string[] ids, int status)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_UpdateAlertStatus",
                                                new SqlParameter("@ids", SqlHelper.ConvertIdsToXML<string>("e", ids)),
                                                new SqlParameter("status", status));
            }
        }

        #endregion


        #region SMS Message


        public List<SMSAlertAllInfo> GetNecessarySMSAlert(Guid alertID, Enums.AlertActionFlag selectFlag, Enums.AlertActionFlag performFlag)
        {
            List<SMSAlertAllInfo> result = new List<SMSAlertAllInfo>();

            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetNecessarySMSAlertInfo",
                    new SqlParameter("@alertID", alertID == Guid.Empty ? (object)DBNull.Value : (object)alertID),
                    new SqlParameter("@selectFlag", selectFlag),
                    new SqlParameter("@updateFlag", performFlag)))
                {
                    if (dr.HasRows)
                    {
                        result = DTOHelper.CreateModelList<SMSAlertAllInfo>(dr);
                    }
                }
            }

            return result;
        }


        public void InertSMSMessageModel(SMSMsg model)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_CreateSMSMsg",
                    new SqlParameter("@SMSAlertID", model.SMSAlertID)
                    , new SqlParameter("@GroupSN", model.GroupSN)
                    , new SqlParameter("@MobileNo", model.MobileNo)
                    , new SqlParameter("@MsgBody", model.MsgBody.Length > 512 ? model.MsgBody.Substring(0, 512) : model.MsgBody)
                    , new SqlParameter("@MsgStatus", model.MsgStatus)
                );

            }
        }


        public void UpdateSMSAlertStatus(string[] IDs, Enums.AlertActionFlag performAction)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_UpdateSMSAlertStatus",
                                                new SqlParameter("@ids", SqlHelper.ConvertIdsToXML<string>("e", IDs)),
                                                new SqlParameter("status", performAction));
            }
        }

        public List<SMSMsg> GetSendSMSMessageList()
        {
            return GetModelList<SMSMsg>("up_GetSMSMsgList");
        }

        public void UpdateSMSMsgStatus(string[] IDs, int Status)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_UpdateSMSMsgtatus",
                                                new SqlParameter("@ids", SqlHelper.ConvertIdsToXML<string>("e", IDs)),
                                                new SqlParameter("status", Status));
            }
        }

        public List<SMSContact> GetContactList(string companyCode)
        {
            List<SMSContact> result = null;
            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetContactListByCode", new SqlParameter("@groupCode", companyCode)))
                {
                    if (dr.HasRows)
                    {
                        result = DTOHelper.CreateModelList<SMSContact>(dr);
                    }
                    else
                    {
                        result = new List<SMSContact>();
                    }
                }
            }
            return result;

        }

        public List<MeteoMsg> GetMeteoListByAirportAndLevel(string airport, DateTime effectiveTime, int warnLevel, Guid msgid)
        {
            List<MeteoMsg> result = null;
            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetMeteoListByAirport",
                              new SqlParameter("@Airport", airport)
                              , new SqlParameter("@WarnTime", effectiveTime)
                              , new SqlParameter("@Warnlevel", warnLevel)
                              , new SqlParameter("@msgid", msgid)))
                {
                    if (dr.HasRows)
                    {
                        result = DTOHelper.CreateModelList<MeteoMsg>(dr);
                    }
                    else
                    {
                        result = new List<MeteoMsg>();
                    }
                }
            }
            return result;

        }

        #endregion

        #region for client

        public List<AlertAllInfo> GetRecentClientAlertInfo()
        {

            List<AlertAllInfo> result = new List<AlertAllInfo>();

            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetClientAlertInfo"))
                {
                    if (dr.HasRows)
                    {
                        result = DTOHelper.CreateModelList<AlertAllInfo>(dr);
                    }
                }
            }



            //step 2: get MessageBody

            //if (result.Count > 0)
            //{
            //    Guid[] ids =  result.Distinct(new Comparint()).ToDictionary<NormalAlertInfo, Guid>(f => f.AlertID).Keys.ToArray();

            //    List<AlertAllInfo> infoList = GetAlertInfoByIDs(ids);

            //    foreach (NormalAlertInfo alertInfo in result)
            //    {
            //        try
            //        {
            //            AlertAllInfo info = infoList.Where(f => f.AlertID == alertInfo.AlertID).ToList()[0];

            //            alertInfo.MessageBody = MessageBodyFactory.GetMessageBody<AlertAllInfo>(info, alertInfo.MsgTemplateContent);
            //        }

            //        catch (Exception e)
            //        {
            //            Logger.Error(string.Format("error in get ClientAlertInfo from DB,AlertID is {0} ", alertInfo.AlertID), e);
            //        }
            //    }
            //}
            return result;

        }

        public List<SMSAlertAllInfo> GetRecentClientSMSAlertInfo()
        {

            List<SMSAlertAllInfo> result = new List<SMSAlertAllInfo>();

            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetClientSMSAlertInfo"))
                {
                    if (dr.HasRows)
                    {
                        result = DTOHelper.CreateModelList<SMSAlertAllInfo>(dr);
                        //    while (dr.Read())
                        //    {
                        //        try
                        //        {
                        //            SMSAlertAllInfo info = DTOHelper.CreateModel2<SMSAlertAllInfo>(dr);

                        //           // info.MessageBody = MessageBodyFactory.GetMessageBody<SMSAlertAllInfo>(info, info.MsgTemplateContent);

                        //            result.Add(info);
                        //        }
                        //        catch (Exception e)
                        //        {
                        //            Logger.Error(string.Format("error in get ClientAlertInfo from DB,AlertID is {0} ", dr["AlertID"].ToString()), e);
                        //        }

                        //    }
                    }
                }
            }

            return result;

        }

        public void WriteActionLog(List<ActionPerformLog> logList)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_InsertActionLog", new SqlParameter("@listXml", SqlHelper.ConvertModelListToXML<ActionPerformLog>("e", logList)));
            }
        }



        public List<AlertAllInfo> GetAlertInfoByIDs(Guid[] ids)
        {

            List<AlertAllInfo> result = null;

            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetAlertInfoByIDs",
                    new SqlParameter("@ids", SqlHelper.ConvertIdsToXML("e", ids))))
                {
                    if (dr.HasRows)
                    {
                        result = DTOHelper.CreateModelList<AlertAllInfo>(dr);
                    }
                }
            }

            return result;

        }
        public List<AlertAllInfo> GetAlertHisInfoByIDs(Guid[] ids)
        {

            List<AlertAllInfo> result = null;

            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetAlertHisInfoByIDs",
                    new SqlParameter("@ids", SqlHelper.ConvertIdsToXML("e", ids))))
                {
                    if (dr.HasRows)
                    {
                        result = DTOHelper.CreateModelList<AlertAllInfo>(dr);
                    }
                }
            }

            return result;

        }
        public List<NormalAlertInfo> GetNormalAlertInfoByIDs(Guid[] ids)
        {

            List<NormalAlertInfo> result = null;

            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetNormalAlertInfoByIDs",
                    new SqlParameter("@ids", SqlHelper.ConvertIdsToXML("e", ids))))
                {
                    if (dr.HasRows)
                    {
                        result = DTOHelper.CreateModelList<NormalAlertInfo>(dr);
                    }
                }
            }

            return result;

        }
        public List<SMSAlertAllInfo> GetSMSAlertInfoByIDs(Guid[] ids)
        {

            List<SMSAlertAllInfo> result = null;

            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetSMSAlertInfoByIDs",
                    new SqlParameter("@ids", SqlHelper.ConvertIdsToXML("e", ids))))
                {
                    if (dr.HasRows)
                    {
                        result = DTOHelper.CreateModelList<SMSAlertAllInfo>(dr);
                    }
                }
            }

            return result;

        }

        #endregion

        #region SMS Alert

        public void InsertSMSAlertModel(SMSAlert sMSAlert)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_CreateSMSAlert"
                    , new SqlParameter("@MeteoMsgID", sMSAlert.MeteoMsgID)
                    , new SqlParameter("@SeatID", sMSAlert.SeatID)
                    , new SqlParameter("@GroupCode", sMSAlert.GroupCode)
                    , new SqlParameter("@RelationFlights", sMSAlert.RelationFlights)
                    , new SqlParameter("@FlightCount", sMSAlert.FlightCount)
                    , new SqlParameter("@AlertActionFlag", sMSAlert.AlertActionFlag)
                    , new SqlParameter("@PerformActionFlag", sMSAlert.PerformActionFlag)
                );
            }
        }

        public List<FlightInfo> GetSMSFlightInfoByMtMsg(string airPort, DateTime startTime, DateTime endTime)
        {
            List<FlightInfo> result = null;
            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetSMSFlightInfoByMtMsg",
                                                                        new SqlParameter("@airPort", airPort),
                                                                        new SqlParameter("@startTime", startTime),
                                                                        new SqlParameter("@endTime", endTime)))
                {
                    if (dr.HasRows)
                    {
                        result = DTOHelper.CreateModelList<FlightInfo>(dr);
                    }
                    else
                    {
                        result = new List<FlightInfo>();
                    }
                    dr.Close();
                }
            }
            return result;
        }

        #endregion

        #region for login

        public List<SeatLogin> GetAllLoginUser(bool? status)
        {
            List<SeatLogin> result = null;
            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetAllLoginUser",
                    new SqlParameter("@status", status == null ? (object)DBNull.Value : (object)status)))
                {
                    if (dr.HasRows)
                    {
                        result = DTOHelper.CreateModelList<SeatLogin>(dr);
                    }
                    else
                    {
                        result = new List<SeatLogin>();
                    }
                    dr.Close();
                }
            }
            return result;
        }

        public bool SeatUserIsLogin(Guid SeatID, string UserName)
        {
            object ob;
            using (SqlConnection conn = GetSqlConnection())
            {
                ob = SqlHelper.ExecuteScalar(conn, "up_CheckUser", new SqlParameter("@SeatID", SeatID)
                                                            , new SqlParameter("@UserName", UserName));
            }
            if (ob == null)
            {
                return false;
            }
            else
            {
                return true;
            }

        }

        public List<SeatRelation> GetSeatRelation()
        {
            return GetModelList<SeatRelation>("up_GetSeatRelation");
        }

        public List<Seat> GetDelegatableSeats(Guid SeatID)
        {
            List<Seat> result = null;

            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetDelegatableSeat", new SqlParameter("@SeatID", SeatID)))
                {
                    try
                    {
                        result = DTOHelper.CreateModelList<Seat>(dr);
                    }
                    catch
                    {
                        result = new List<Seat>();
                    }
                }
            }

            return result;
        }

        public string InsertDelegateRelation(SeatRelation Model)
        {
            object result;
            using (SqlConnection conn = GetSqlConnection())
            {
                result = SqlHelper.ExecuteScalar(conn, "up_InsertDelegateRelation",
                                                   new SqlParameter("@SelfSeatID", Model.SelfSeatID),
                                                   new SqlParameter("@DelegateSeatID", Model.DelegateSeatID),
                                                   new SqlParameter("@ApplyUserID", Model.ApplyUserID),
                                                   new SqlParameter("@ConfirmStatus", Model.ConfirmStatus),
                                                   new SqlParameter("@Comment", Model.Comment));
            }
            return result.ToString();
        }

        public void UpdateDelegateStatus(Guid RelationID, int Status)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_UpdateDelegateStatus", new SqlParameter("@RelationID", RelationID),
                                                    new SqlParameter("@ConfirmStatus", Status));
            }
        }

        #endregion


        #region for Seat Plane manage

        public List<SeatTails> GetAllSeatTails()
        {
            return GetModelList<SeatTails>("up_GetAllSeatTails");
        }

        public List<PlaneType> GetAllPlaneType()
        {
            return GetModelList<PlaneType>("up_GetAllPlaneType");
        }

        public string SaveSeatTails(Guid SeatID, string[] Tails)
        {
            string result = "";
            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_SaveSeatTails", new SqlParameter("@SeatID", SeatID),
                                                                            new SqlParameter("@Tails", SqlHelper.ConvertIdsToXML("e", Tails))))
                {
                    try
                    {
                        if (dr.Read())
                        {
                            result = dr["TailStr"].ToString();
                        }
                    }
                    catch (Exception e)
                    {
                        Logger.Error(e);

                    }
                }
            }

            return result;
        }


        public string InsertPlaneType(PlaneType model)
        {
            object result;
            using (SqlConnection conn = GetSqlConnection())
            {
                result = SqlHelper.ExecuteScalar(conn, "up_CreatePlaneType", new SqlParameter("@ParentID", model.ParentID),
                                                       new SqlParameter("@TypeName", model.TypeName),
                                                       new SqlParameter("@Description", model.Description));
            }

            return result.ToString();
        }

        public string UpdatePlaneType(PlaneType model)
        {
            object result;
            using (SqlConnection conn = GetSqlConnection())
            {
                result = SqlHelper.ExecuteScalar(conn, "up_UpdatePlaneType", new SqlParameter("@PlaneTypeID", model.PlaneTypeID),
                                                       new SqlParameter("@TypeName", model.TypeName),
                                                       new SqlParameter("@Description", model.Description));
            }

            return result.ToString();
        }

        public string InsertPlaneInfo(ACARSDevice model)
        {
            object result;
            using (SqlConnection conn = GetSqlConnection())
            {
                result = SqlHelper.ExecuteScalar(conn, "up_CreateACARSDevice", new SqlParameter("@TailNo", model.TailNo),
                                                       new SqlParameter("@PlaneTypeID", model.PlaneTypeID),
                                                       new SqlParameter("@CompanyCode", model.CompanyCode));
            }

            return result.ToString();
        }

        public string UpdatePlaneInfo(ACARSDevice model)
        {
            object result;
            using (SqlConnection conn = GetSqlConnection())
            {
                result = SqlHelper.ExecuteScalar(conn, "up_UpdateACARSDevice", new SqlParameter("@TailNo", model.TailNo),
                                                       new SqlParameter("@PlaneTypeID", model.PlaneTypeID),
                                                       new SqlParameter("@CompanyCode", model.CompanyCode));
            }

            return result.ToString();
        }

        public bool DeletePlaneType(int TypeID, string ParentOrChild)
        {
            try
            {
                using (SqlConnection conn = GetSqlConnection())
                {
                    SqlHelper.ExecuteNonQuery(conn, "up_DeletePlaneType", new SqlParameter("@PlaneTypeID", TypeID));
                }
                return true;
            }
            catch (Exception e)
            {
                Logger.Error(e);
                return false;
            }
        }

        public bool DeletePlaneInfo(string tailNo)
        {
            try
            {
                using (SqlConnection conn = GetSqlConnection())
                {
                    SqlHelper.ExecuteNonQuery(conn, "up_DeletePlaneInfo", new SqlParameter("@TailNo", tailNo));
                }
                return true;
            }
            catch (Exception e)
            {
                Logger.Error(e);
                return false;
            }
        }

        #endregion

        #endregion

        #region MeteoMsg 王劲松
        #region ftp报文解析
        public Dictionary<string, DateTime> GetRecentFileNames(int recentDays)
        {
            Dictionary<string, DateTime> names = null;
            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetRecentFileNames", new SqlParameter("@DayCount", recentDays)))
                {
                    names = SqlHelper.PopulateReadersToDic<string, DateTime>(dr, "FileName", "FileCreateTime");
                }
                conn.Close();
            }
            return names;
        }
        public MeteoMsg CreateMeteoMsg(MeteoMsg meteoMsg)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlParameter spFileId = new SqlParameter("@MetroMsgID", SqlDbType.Int);
                spFileId.Direction = ParameterDirection.Output;
                spFileId.Value = meteoMsg.MetroMsgID;

                SqlHelper.ExecuteNonQuery(conn, "up_CreateFTPMeteoMsg",
                    spFileId
                    , new SqlParameter("@Airport", meteoMsg.Airport)
                    , new SqlParameter("@WarnTime", meteoMsg.WarnTime)
                    , new SqlParameter("@WarnLevel", meteoMsg.WarnLevel)
                    , new SqlParameter("@WarnMsg", meteoMsg.WarnMsg)
                    , new SqlParameter("@WarnInfo", meteoMsg.WarnInfo)
                    , new SqlParameter("@WarnStandard", meteoMsg.WarnStandard)
                    , new SqlParameter("@FileName", meteoMsg.FileName)
                    , new SqlParameter("@FileCreateTime", meteoMsg.FileCreateTime)
                    , new SqlParameter("@FileDownloadTime", meteoMsg.FileDownloadTime)
                    //, new SqlParameter("@AlertCreateTime", meteoMsg.AlertCreateTime)
                    , new SqlParameter("@MsgStatus", meteoMsg.MsgStatus)
                    , new SqlParameter("@LastModifyTime", meteoMsg.LastModifyTime)
                    , new SqlParameter("@DeleteFlag", meteoMsg.DeleteFlag)
               );
                //meteoMsg.MetroMsgID = (Guid)spFileId.Value;
                return meteoMsg;
            }
        }
        #endregion

        #region 报警规则（AlertRule）

        public List<AlertRule> GetAllAlertRuleRecords()
        {
            return GetModelList<AlertRule>("up_GetAllAlertRule");
        }


        public bool CreateAlertRule(AlertRule alertRule)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_CreateAlertRule",
                    new SqlParameter("@Airport", alertRule.Airport)
                    , new SqlParameter("@WarnLevel", alertRule.WarnLevel)
                    , new SqlParameter("@AlertActionFlag", alertRule.AlertActionFlag)
                );
            }
            return true;
        }


        public bool UpdateAlertRule(AlertRule alertRule)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_UpdateAlertRule",
                    new SqlParameter("@AlertRuleID", alertRule.AlertRuleID)
                    , new SqlParameter("@Airport", alertRule.Airport)
                    , new SqlParameter("@WarnLevel", alertRule.WarnLevel)
                    , new SqlParameter("@AlertActionFlag", alertRule.AlertActionFlag)
                );
            }
            return true;
        }

        public bool DeleteAlertRule(Guid alertRuleID)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_DeleteAlertRule",
                    new SqlParameter("@AlertRuleID", alertRuleID)
                );
            }
            return true;
        }

        #endregion


        #region 消息模板（MsgTemplate）

        public List<MsgTemplate> GetAllMsgTemplateRecords()
        {
            return GetModelList<MsgTemplate>("up_GetAllMsgTemplate");
        }


        public bool CreateMsgTemplate(MsgTemplate msgTemplate)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_CreateMsgTemplate",
                    new SqlParameter("@AlertAction", msgTemplate.AlertAction)
                    , new SqlParameter("@MsgContent", msgTemplate.MsgContent)
                     , new SqlParameter("@TargetType", msgTemplate.TargetType)
                );
            }
            return true;
        }


        public bool UpdateMsgTemplate(MsgTemplate msgTemplate)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_UpdateMsgTemplate",
                    new SqlParameter("@MsgTemplateID", msgTemplate.MsgTemplateID)
                    , new SqlParameter("@AlertAction", msgTemplate.AlertAction)
                    , new SqlParameter("@MsgContent", msgTemplate.MsgContent)
                    , new SqlParameter("@TargetType", msgTemplate.TargetType)
                );
            }
            return true;
        }

        public bool DeleteMsgTemplate(Guid msgTemplateID)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_DeleteMsgTemplate",
                    new SqlParameter("@MsgTemplateID", msgTemplateID)
                );
            }
            return true;
        }

        #endregion


        #region 警告方式定义（AlertActionDefine）
        public List<AlertActionDefine> GetAllAlertActionDefineRecords()
        {
            return GetModelList<AlertActionDefine>("up_GetAllAlertActionDefine");
        }
        #endregion


        #region 预警时间设置（EarlyWarning）
        public List<EarlyWarning> GetAllEarlyWarningRecords()
        {
            return GetModelList<EarlyWarning>("up_GetAllEarlyWarning");
        }


        public bool CreateEarlyWarning(EarlyWarning earlyWarning)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_CreateEarlyWarning",
                    new SqlParameter("@Airport", earlyWarning.Airport)
                    , new SqlParameter("@AOCEarlyTime", earlyWarning.AOCEarlyTime)
                    , new SqlParameter("@SMSEarlyTime", earlyWarning.SMSEarlyTime)
                );
            }
            return true;
        }


        public bool UpdateEarlyWarning(EarlyWarning earlyWarning)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_UpdateEarlyWarning",
                    new SqlParameter("@EarlyWarningID", earlyWarning.EarlyWarningID)
                    , new SqlParameter("@Airport", earlyWarning.Airport)
                    , new SqlParameter("@AOCEarlyTime", earlyWarning.AOCEarlyTime)
                    , new SqlParameter("@SMSEarlyTime", earlyWarning.SMSEarlyTime)
                );
            }
            return true;
        }

        public bool DeleteEarlyWarning(Guid EarlyWarningID)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_DeleteEarlyWarning",
                    new SqlParameter("@EarlyWarningID", EarlyWarningID)
                );
            }
            return true;
        }

        #endregion
        #region 更新备降场信息（FlightInfo）
        public bool UpdateFlightBackUpInfo(FlightInfo flightInfo)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_UpdateFlightBackUpInfo",
                    new SqlParameter("@FlightInfoID", flightInfo.FlightInfoID)
                    , new SqlParameter("@DeptBackup", flightInfo.DeptBackup)
                    , new SqlParameter("@ArrBackup", flightInfo.ArrBackup)
                    , new SqlParameter("@RouteBackup", flightInfo.RouteBackup)
                );
            }
            return true;
        }

        public List<FlightInfo> GetFlightInfoByPara(string carrier, string flightNo, DateTime planDeptTime, string deptAirport, int flag)
        {
            List<FlightInfo> result = null;
            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetFlightInfoByPara",
                    new SqlParameter("@Carrier", carrier),
                    new SqlParameter("@FlightNo", flightNo),
                    new SqlParameter("@PlanDeptTime", planDeptTime),
                                        new SqlParameter("@DeptAirport", deptAirport),
                                        new SqlParameter("@Flag", flag)))
                {
                    if (dr.HasRows)
                    {
                        result = DTOHelper.CreateModelList<FlightInfo>(dr);
                    }
                    else
                    {
                        result = new List<FlightInfo>();
                    }
                    dr.Close();
                }
            }
            return result;
        }

        public List<MeteoMsg> GetMeteoMsgListByPara(int relationType, string airport, DateTime? effectTime)
        {
            List<MeteoMsg> result = null;
            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetMeteoMsgListByPara",
                    new SqlParameter("@RelationType", relationType),
                    new SqlParameter("@Airport", string.IsNullOrEmpty(airport) ? (object)DBNull.Value : airport),
                    new SqlParameter("@Effectime", effectTime == null ? (object)DBNull.Value : (object)effectTime)))
                {
                    if (dr.HasRows)
                    {
                        result = DTOHelper.CreateModelList<MeteoMsg>(dr);
                    }
                    else
                    {
                        result = new List<MeteoMsg>();
                    }
                    dr.Close();
                }
            }
            return result;
        }


        #endregion

        #region 系统日志
        public void InsertSystemLog(List<SystemLog> logList)
        {

            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_InsertSystemLog", new SqlParameter("@systemLogXml", SqlHelper.ConvertModelListToXML<SystemLog>("element", logList)));
            }
        }
        #endregion

        #region 查询日志



        public object QueryLogCommon(string logTypeStr, List<QueryCondition> qc)
        {
            object result = null;
            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_QueryLogCommon",
                    new SqlParameter("@LogTypeStr", logTypeStr),
                    new SqlParameter("@QueryConditionXml", SqlHelper.ConvertModelListToXML<QueryCondition>("element", qc))))
                {
                    switch (logTypeStr)
                    {
                        case "ACTIONPERFORMLOG":
                            if (dr.HasRows)
                            {
                                result = DTOHelper.CreateModelList<QueryActLog>(dr);
                            }
                            else
                            {
                                result = new List<QueryActLog>();
                            }
                            break;
                        case "SEATLOGINLOG":
                            if (dr.HasRows)
                            {
                                result = DTOHelper.CreateModelList<QuerySeatLogin>(dr);
                            }
                            else
                            {
                                result = new List<QuerySeatLogin>();
                            }
                            break;
                        case "SYSTEMLOG":
                            if (dr.HasRows)
                            {
                                result = DTOHelper.CreateModelList<QuerySystemLog>(dr);
                            }
                            else
                            {
                                result = new List<QuerySystemLog>();
                            }
                            break;
                        case "ACTANDSYS":
                            if (dr.HasRows)
                            {
                                result = DTOHelper.CreateModelList<QueryLoginAndSystemLog>(dr);
                            }
                            else
                            {
                                result = new List<QueryLoginAndSystemLog>();
                            }
                            break;


                        default:

                            break;



                    }


                    dr.Close();
                }
            }
            return result;
        }



        public List<NormalAlertInfo> GetAllAlertInfo()
        {

            List<NormalAlertInfo> result = new List<NormalAlertInfo>();

            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetAllAlertInfo"))
                {
                    if (dr.HasRows)
                    {
                        result = DTOHelper.CreateModelList<NormalAlertInfo>(dr);
                    }
                }
            }
            return result;

        }

        public List<SMSAlertAllInfo> GetAllSMSAlertInfo()
        {

            List<SMSAlertAllInfo> result = new List<SMSAlertAllInfo>();

            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetAllSMSAlertInfo"))
                {
                    if (dr.HasRows)
                    {
                        result = DTOHelper.CreateModelList<SMSAlertAllInfo>(dr);
                    }
                }
            }

            return result;

        }





        #endregion
        #endregion

        #region FlightInfo 张希贵

        #endregion

        #region SeatManage 张希贵
        public List<SMSContact> GetAllSMSContacts()
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                return GetModelList<SMSContact>("up_GetAllSMSContacts");
            }
        }
        public bool CreateSMSContact(SMSContact contact)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_CreateSMSContact",
                      new SqlParameter("@ContactName", contact.ContactName)
                      , new SqlParameter("@ContactMobile", contact.ContactMobile)
                      , new SqlParameter("@SMSContactGroupID", contact.SMSContactGroupID)
                  );
                return true;
            }
        }
        public bool DeleteSMSContact(Guid contactID)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_DeleteSMSContact",
                      new SqlParameter("@SMSContactID", contactID)
                  );
                return true;
            }
        }
        public bool UpdateSMSContact(SMSContact contact)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_UpdateSMSContact",
                      new SqlParameter("@SMSContactID", contact.SMSContactID)
                      , new SqlParameter("@ContactName", contact.ContactName)
                      , new SqlParameter("@ContactMobile", contact.ContactMobile)
                      , new SqlParameter("@SMSContactGroupID", contact.SMSContactGroupID)
                  );
                return true;
            }
        }

        public Model.Enums.DataAccessReturnValue ChangePassword(SeatUser seatUser, byte[] oldPassword)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlParameter isValidUser = new SqlParameter("@IsValidUser", SqlDbType.Bit);
                isValidUser.Direction = ParameterDirection.Output;
                isValidUser.Value = 0;
                SqlHelper.ExecuteNonQuery(conn, "up_ChangeSeatUserPassword",
                      isValidUser
                      , new SqlParameter("@UserName", seatUser.UserName)
                      , new SqlParameter("@Password", seatUser.Password)
                      , new SqlParameter("@OldPassword", oldPassword)
                  );
                if (System.Convert.ToBoolean(isValidUser.Value) == true)
                {
                    return Model.Enums.DataAccessReturnValue.更新成功;
                }
                else
                {
                    return Model.Enums.DataAccessReturnValue.无法更新;
                }
            }
        }

        public bool CreateSeatUser(SeatUser seatUser)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlParameter isUserNameExist = new SqlParameter("@IsExist", SqlDbType.Bit);
                isUserNameExist.Direction = ParameterDirection.Output;
                isUserNameExist.Value = 0;
                SqlHelper.ExecuteNonQuery(conn, "up_CreateSeatUser",
                      new SqlParameter("@UserName", seatUser.UserName)
                      , isUserNameExist
                      , new SqlParameter("@Password", seatUser.Password)
                      , new SqlParameter("@RoleID", seatUser.RoleID)
                  );
                if (System.Convert.ToBoolean(isUserNameExist.Value) == true)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
        }

        public bool CreateSeat(Seat seat)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_CreateSeat",
                      new SqlParameter("@SeatName", seat.SeatName)
                      , new SqlParameter("@MAC", seat.MAC)
                  );
            }
            return true;
        }
        public bool DeleteSeat(Seat seat)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_DeleteSeat",
                      new SqlParameter("@SeatID", seat.SeatID)
                  );
            }
            return true;
        }
        public bool UpdateSeat(Seat seat)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_UpdateSeat",
                      new SqlParameter("@SeatID", seat.SeatID)
                      , new SqlParameter("@SeatName", seat.SeatName)
                      , new SqlParameter("@MAC", seat.MAC)
                  );
            }
            return true;
        }

        public bool UpdateSeatUser(SeatUser seatUser)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlParameter isUserNameExist = new SqlParameter("@IsUserNameExist", SqlDbType.Bit);
                isUserNameExist.Direction = ParameterDirection.Output;
                isUserNameExist.Value = 0;
                if (seatUser.Password != null)
                {
                    SqlHelper.ExecuteNonQuery(conn, "up_UpdateSeatUserWithPassword",
                     isUserNameExist
                     , new SqlParameter("@UserID", seatUser.UserID)
                     , new SqlParameter("@UserName", seatUser.UserName)
                     , new SqlParameter("@Password", seatUser.Password)
                     , new SqlParameter("@RoleID", seatUser.RoleID)
                 );
                }
                else
                {
                    SqlHelper.ExecuteNonQuery(conn, "up_UpdateSeatUser",
                        isUserNameExist
                        , new SqlParameter("@UserID", seatUser.UserID)
                        , new SqlParameter("@UserName", seatUser.UserName)
                        , new SqlParameter("@RoleID", seatUser.RoleID)
                    );
                }
                if (System.Convert.ToBoolean(isUserNameExist.Value) == true)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
        }
        public bool DeleteSeatUser(Guid userID, bool deleteFlag)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_DeleteSeatUser",
                    new SqlParameter("@UserID", userID)
                    , new SqlParameter("@DeleteFlag", deleteFlag)
                );
            }
            return true;
        }
        public List<SMSContactGroup> GetContactGroup()
        {
            return GetModelList<SMSContactGroup>("up_GetSMSContactGroups");
        }
        public Enums.DataAccessReturnValue CreateSMSContactGroup(SMSContactGroup group)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlParameter isGroupCodeExist = new SqlParameter("@IsGroupCodeExist", SqlDbType.Bit);
                isGroupCodeExist.Direction = ParameterDirection.Output;
                isGroupCodeExist.Value = 0;
                SqlHelper.ExecuteNonQuery(conn, "up_CreateSMSContactGroup",
                   isGroupCodeExist
                   , new SqlParameter("@GroupName", group.GroupName)
                   , new SqlParameter("@GroupCode", group.GroupCode)
                );
                if (System.Convert.ToBoolean(isGroupCodeExist.Value) == true)
                {
                    return Enums.DataAccessReturnValue.无法更新;
                }
                else
                {
                    return Enums.DataAccessReturnValue.更新成功;
                }
            }
        }
        public Enums.DataAccessReturnValue UpdateSMSContactGroup(SMSContactGroup group)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlParameter isGroupCodeExist = new SqlParameter("@IsGroupCodeExist", SqlDbType.Bit);
                isGroupCodeExist.Direction = ParameterDirection.Output;
                isGroupCodeExist.Value = 0;
                SqlHelper.ExecuteNonQuery(conn, "up_UpdateSMSContactGroup",
                   isGroupCodeExist
                   , new SqlParameter("@SMSContactGroupID", group.SMSContactGroupID)
                   , new SqlParameter("@GroupName", group.GroupName)
                   , new SqlParameter("@GroupCode", group.GroupCode)
                );
                if (System.Convert.ToBoolean(isGroupCodeExist.Value) == true)
                {
                    return Enums.DataAccessReturnValue.无法更新;
                }
                else
                {
                    return Enums.DataAccessReturnValue.更新成功;
                }
            }
        }
        public bool DeleteSMSContactGroup(Guid groupID)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_DeleteSMSContactGroup",
                    new SqlParameter("@SMSContactGroupID", groupID)
                );
            }
            return true;
        }
        public List<SeatUser> GetAllSeatUser(int flag)
        {

            List<SeatUser> result = null;
            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetAllSeatUser",
                    new SqlParameter("@Flag", flag)))
                {
                    if (dr.HasRows)
                    {
                        result = DTOHelper.CreateModelList<SeatUser>(dr);
                    }
                    else
                    {
                        result = new List<SeatUser>();
                    }
                    dr.Close();
                }
            }
            return result;

        }

        public List<SeatRole> GetAllSeatRole()
        {
            return GetModelList<SeatRole>("up_GetAllSeatRole");
        }

        /// <summary>
        /// Create SeatRole
        /// </summary>
        /// <param name="seatRole"></param>
        public bool CreateSeatRole(SeatRole seatRole)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_CreateSeatRole",
                    new SqlParameter("@RoleName", seatRole.RoleName)
                    , new SqlParameter("@PowerFlag", seatRole.PowerFlag)
                );
            }
            return true;
        }

        /// <summary>
        /// Update Seat Role
        /// </summary>
        /// <param name="seatRole"></param>
        public bool UpdateSeatRole(SeatRole seatRole)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_UpdateSeatRole",
                    new SqlParameter("@RoleID", seatRole.RoleID)
                    , new SqlParameter("@RoleName", seatRole.RoleName)
                    , new SqlParameter("@PowerFlag", seatRole.PowerFlag)
                );
            }
            return true;
        }
        /// <summary>
        /// Delete SeatRole
        /// </summary>
        /// <param name="roleID"></param>
        public bool DeleteSeatRole(Guid roleID)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_DeleteSeatRole",
                    new SqlParameter("@RoleID", roleID)
                );
            }
            return true;
        }

        public List<SystemModule> GetSystemModules()
        {
            return GetModelList<SystemModule>("up_GetSystemModule");
        }

        public List<SeatMonitor> GetSeatMonitors()
        {
            return GetModelList<SeatMonitor>("up_GetAllSeatMonitors");
        }
        public DispatchAssist.BusinessLogical.Model.Enums.DataAccessReturnValue CreateSeatMonitor(SeatMonitor seatMonitor)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlParameter isPlaneExist = new SqlParameter("@IsPlaneExist", SqlDbType.Bit);
                isPlaneExist.Direction = ParameterDirection.Output;
                isPlaneExist.Value = 0;
                SqlHelper.ExecuteNonQuery(conn, "up_CreateSeatMonitor",
                    isPlaneExist
                    , new SqlParameter("@SeatID", seatMonitor.SeatID)
                    , new SqlParameter("@TailNo", seatMonitor.TailNo)
                );
                if (System.Convert.ToBoolean(isPlaneExist.Value) == true)
                {
                    return DispatchAssist.BusinessLogical.Model.Enums.DataAccessReturnValue.无法更新;
                }
                else
                {
                    return DispatchAssist.BusinessLogical.Model.Enums.DataAccessReturnValue.更新成功;
                }
            }
        }
        public bool DeleteSeatMonitor(Guid seatMonitorID)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_DeleteSeatMonitor",
                    new SqlParameter("@SeatMonitorID", seatMonitorID)
                );
            }
            return true;
        }
        #endregion

        #region Login/Logout 张晓磊

        public SeatUser Login(string userName, byte[] password, Guid seatId)
        {

            SeatUser result = null;

            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_Login",
                    new SqlParameter("@UserName", userName)
                    , new SqlParameter("@Password", password)
                    , new SqlParameter("@SeatID", seatId)))
                {

                    if (dr.Read())
                    {
                        result = new SeatUser(dr);
                    }
                    else
                    {
                        return null;
                    }

                    if (dr.NextResult())
                    {
                        try
                        {
                            result.DelegateSeats = DTOHelper.CreateModelList<Seat>(dr);
                        }
                        catch
                        {
                            result.DelegateSeats = null;
                        }
                    }
                    else
                    {
                        result.DelegateSeats = null;
                    }
                }
            }
            return result;
        }

        public bool Logout(Guid userId, Guid seatId)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_Logout",
                    new SqlParameter("@UserID", userId)
                    , new SqlParameter("@SeatId", seatId)
                );
            }
            return true;
        }

        #endregion

        #region Common

        public List<InfoAirport> LoadInfoAirportList()
        {
            List<InfoAirport> list = null;
            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetAllInfoAirport"))
                {
                    list = DTOHelper.CreateModelList<InfoAirport>(dr);
                }
                conn.Close();
            }
            return list;
        }

        public List<T> GetModelList<T>(string procedure)
        {
            List<T> list = null;
            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, procedure))
                {
                    list = DTOHelper.CreateModelList<T>(dr);
                }
                conn.Close();
            }
            return list;
        }

        public string GetTablePKColumn(string tableName)
        {
            string keyName = null;
            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "sp_pkeys", new SqlParameter("@table_name", tableName)))
                {
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            keyName = dr["COLUMN_NAME"].ToString();
                        }
                    }
                }
            }
            return keyName;
        }

        #endregion



    }

    public class Comparint : IEqualityComparer<NormalAlertInfo>
    {

        public bool Equals(NormalAlertInfo x, NormalAlertInfo y)
        {
            if (x == null && y == null)
                return false;
            return x.AlertID == y.AlertID;
        }

        public int GetHashCode(NormalAlertInfo obj)
        {
            return obj.ToString().GetHashCode();
        }
    }
}
